﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LTSMZ.life.Model;
using System.Data.SqlClient;
using System.Data;
using Qianli.DirSir.Common;

namespace LTSMZ.life.DAL
{
    public class FirstTypeDal
    {
        SQLHelper sqlhelper = new SQLHelper();
        /// <summary>
        /// 一级类别显示
        /// </summary>
        /// <returns></returns>
        public List<FirstType> FirstTypeShow()
        {
            string sql = "select FirstType.Fid,FirstType.Fname from FirstType";
            DataTable dt = sqlhelper.ExecDataTable(sql);
            List<FirstType> list = new List<FirstType>();
            foreach (DataRow d in dt.Rows)
            {
                FirstType f = new FirstType();
                f.Fid = Convert.ToInt32(d["Fid"]);
                f.Fname = d["Fname"].ToString();
                list.Add(f);
            }
            return list;
        }
        /// <summary>
        /// 添加一级类别
        /// </summary>
        /// <param name="f"></param>
        /// <returns></returns>
        //public int AddFirstType(FirstType f)
        //{
        //    string sql = string.Format("insert into FirstType values('{0}')", f.Fname);
        //    return sqlhelper.ExecNonquery(sql);
        //}
        /// <summary>
        /// 删除一级类别
        /// </summary>
        /// <param name="Fid"></param>
        /// <returns></returns>
        //public int DelFirstType(int Fid)
        //{
        //    string sql = "delete from FirstType where Fid=" + Fid + "";
        //    return sqlhelper.ExecNonquery(sql);
        //}
        /// <summary>
        /// 根据编号显示一级类别
        /// </summary>
        /// <param name="Fid"></param>
        /// <returns></returns>
        //public FirstType GetFirstType(int Fid)
        //{
        //    string sql = "select FirstType.Fid,FirstType.Fname from FirstType where Fid=" + Fid + "";
        //    SqlDataReader reader = sqlhelper.ExecReader(sql);
        //    FirstType f = new FirstType();
        //    while (reader.Read())
        //    {
        //        f.Fid = Convert.ToInt32(reader["Fid"]);
        //        f.Fname = reader["Fname"].ToString();
        //    }
        //    return f;
        //}
        /// <summary>
        /// 修改一级类别
        /// </summary>
        /// <param name="f"></param>
        /// <returns></returns>
        //public int UptFirstType(FirstType f)
        //{
        //    string sql = "UPDATE FirstType set Fname='" + f.Fname + "' where Fid=" + f.Fid + "";
        //    return sqlhelper.ExecNonquery(sql);
        //}
        /// <summary>
        /// 添加第一类别（存储过程）
        /// </summary>
        /// <param name="Fname"></param>
        /// <returns></returns>
        public int Add_FirsType(string Fname)
        {
            //连接SQL Server数据库
            SqlConnection con = new SqlConnection("Data Source=169.254.236.246;Initial Catalog=lift;User ID=sa;pwd=123123");
            SqlCommand com = new SqlCommand();
            //连接数据库
            com.Connection = con;
            //声明存储过程名称
            com.CommandText = "Add_firsType";
            //声明为存储过程
            com.CommandType = CommandType.StoredProcedure;
            //创建参数
            IDataParameter[] parmeter ={
                new SqlParameter("@Fname",SqlDbType.VarChar,20)
                                      };
            parmeter[0].Value = Fname;
            //添加值
            com.Parameters.Add(parmeter[0]);
            con.Open();
            //返回受影响行数
            int i = com.ExecuteNonQuery();
            con.Close();
            return i;
        }
        /// <summary>
        /// 删除第一类别（存储过程）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Del_FirsType(int id)
        {
            SqlConnection con = new SqlConnection("Data Source=169.254.236.246;Initial Catalog=lift;User ID=sa;pwd=123123");
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandText = "Del_FirsType";
            com.CommandType = CommandType.StoredProcedure;
            IDataParameter[] parmeter ={
                                          new SqlParameter("@Fid",SqlDbType.Int,4)
                                      };
            parmeter[0].Value=id;
            com.Parameters.Add(parmeter[0]);
            con.Open();
            int i=com.ExecuteNonQuery();
            con.Close();
            return i;
        }
        /// <summary>
        /// 根据编号获取第一类别的信息（存储过程）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public FirstType Get_FirsType(int id)
        {
            SqlConnection con = new SqlConnection("Data Source=169.254.236.246;Initial Catalog=lift;User ID=sa;pwd=123123");
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandText = "Get_FirsType";
            com.CommandType = CommandType.StoredProcedure;
            IDataParameter[] parmeter ={
                                           new SqlParameter("@Fid",SqlDbType.Int,4)
                                      };
            parmeter[0].Value=id;
            com.Parameters.Add(parmeter[0]);
            con.Open();
            SqlDataReader reader= com.ExecuteReader();
            FirstType f=new FirstType();
            while (reader.Read())
            {
                f.Fid=Convert.ToInt32(reader["Fid"]);
                f.Fname=reader["Fname"].ToString();
            }
            con.Close();
            return f;
        }
        /// <summary>
        /// 修改第一类别sqlhelper帮助类（存储过程）
        /// </summary>
        /// <param name="f"></param>
        /// <returns></returns>
        public int Upt_FitsType(FirstType f)
        {
            //创建参数
            SqlParameter[] partemert ={
                                          new SqlParameter("@Fname",SqlDbType.VarChar,20),
                                          new SqlParameter("@Fid",SqlDbType.Int,4)
                                      };
            //赋值
            partemert[0].Value = f.Fname;
            partemert[1].Value = f.Fid;
            //sqlhelper帮助类（ExecNonquery+“Proc”）为sqlhelper内封装的存储过程方法
            return sqlhelper.ExecNonquery_Proc("Upt_FirsType", partemert);

        }
        /// <summary>
        /// 显示五个类别名称
        /// </summary>
        /// <returns></returns>
        public List<FirstType> ShowFirsTypeTop5()
        {
            string str = "select * from FirstType where Fid between 5 and 11";
            DataTable dt = sqlhelper.ExecDataTable(str);
            List<FirstType> list = new List<FirstType>();
            foreach (DataRow d in dt.Rows)
            {
                FirstType f = new FirstType();
                f.Fid = Convert.ToInt32(d["Fid"]);
                f.Fname = d["Fname"].ToString();
                list.Add(f);
            }
            return list;
        }
    }
}

